Tutorials and examples - How do I ... ?
Preface
This section enlists a set of answers on common How do I ... ? questions related to the generated code and the functionality LLBLGen Pro provides and
a list of all the example code inside the various sections in this documentation. The
How do I...? examples are given in C# and VB.NET and if required
in both template group semantics, and if not, SelfServicing is used. All examples use the Northwind database schema, unless stated otherwise.
Examples in the documentation
Below you'll find an extensive list of all the example snippets located in this documentation. This list is to give you a quick access to code snippets
to perform a well defined goal without having to read all the documentation first to locate the examples. It's recommended that you read the paragraph in which the
example is located as well, to get more information about when the example is useful. The examples are stored in the order in which they appear in the documentation
if you read everything from top to bottom.
Using the generated code, SelfServicing specific
Configuration
Fetching entities, typed views, typed lists and projections
Create / Update / Delete operations on entities
Entity / Entity view / Entity collection classes features
Filtering and sorting specifications
Using the generated code, Adapter specific
Configuration
Fetching entities, typed views, typed lists and projections
Create / Update / Delete operations on entities
Entity / Entity view / Entity collection classes features
Filtering and sorting specifications
Using the generated code, general
Configuration
Expressions and Aggregate functions
Miscellaneous
Using the generated code, QuerySpec
Using the generated code, Linq to LLBLGen Pro
Getting started
General usage
Prefetch paths
Function mappings
How do I ... ?
How do I read all entities of a given type into a collection?
Selfservicing
Load all customers into a collection
// C#
CustomerCollection allCustomers = new CustomerCollection();
allCustomers.GetMulti(null);
' VB.NET
Dim allCustomers As New CustomerCollection()
allCustomers.GetMulti(Nothing)
Adapter
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
EntityCollection allCustomers = new EntityCollection(new CustomerEntityFactory());
adapter.FetchEntityCollection(allCustomers, null);
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim allCustomers As New EntityCollection(New CustomerEntityFactory())
adapter.FetchEntityCollection(allCustomers, Nothing)
How do I insert a new entity?
Selfservicing
Inserts a new product entity
// C#
ProductEntity newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.QuantityPerUnit="1";
newProduct.ReorderLevel=10;
newProduct.SupplierID=1;
newProduct.UnitPrice=2.13M;
newProduct.UnitsInStock = 50;
newProduct.UnitsOnOrder=0;
newProduct.Save();
'VB.NET
Dim newProduct As New ProductEntity()
newProduct.CategoryID=1
newProduct.Discontinued=False;
newProduct.ProductName="TooCool"
newProduct.QuantityPerUnit="1"
newProduct.ReorderLevel=10
newProduct.SupplierID=1
newProduct.UnitPrice=2.13D
newProduct.UnitsInStock = 50
newProduct.UnitsOnOrder=0
newProduct.Save()
Adapter
(entity is not fetched back)
// C#
ProductEntity newProduct = new ProductEntity();
newProduct.CategoryID=1;
newProduct.Discontinued=false;
newProduct.ProductName="TooCool";
newProduct.QuantityPerUnit="1";
newProduct.ReorderLevel=10;
newProduct.SupplierID=1;
newProduct.UnitPrice=2.13M;
newProduct.UnitsInStock = 50;
newProduct.UnitsOnOrder=0;
DataAccessAdapter adapter = new DataAccessAdapter();
// if the newProduct object is used after this call, specify True
// for refetchAfterSave, a parameter of SaveEntity()
adapter.SaveEntity(newProduct);
'VB.NET
Dim newProduct As New ProductEntity()
newProduct.CategoryID=1
newProduct.Discontinued=False;
newProduct.ProductName="TooCool"
newProduct.QuantityPerUnit="1"
newProduct.ReorderLevel=10
newProduct.SupplierID=1
newProduct.UnitPrice=2.13D
newProduct.UnitsInStock = 50
newProduct.UnitsOnOrder=0
Dim adapter As New DataAccessAdapter()
' if the newProduct object is used after this call, specify True
' for refetchAfterSave, a parameter of SaveEntity()
adapter.SaveEntity(newProduct)
How do I delete an existing entity?
Method 1: entity is already loaded into memory.
Selfservicing
Deleting the customer "FISSA" from Northwind (doesn't violate any FK constraints)
// C#
// fetch the entity.
CustomerEntity customer = new CustomerEntity("FISSA");
// ... some other code
customer.Delete();' VB.NET
' fetch the entity.
Dim customer As New CustomerEntity("FISSA")
' ... some other code
customer.Delete()
Adapter
// C#
// fetch the entity.
CustomerEntity customer = new CustomerEntity("FISSA");
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(customer);
// ... some other code
adapter.DeleteEntity(customer);' VB.NET
' fetch the entity.
Dim customer As New CustomerEntity("FISSA")
Dim adapter As New DataAccessAdapter()
adapter.FetchEntity(customer)
' ... some other code
adapter.DeleteEntity(customer)
Method 2: use a new entity object to directly delete an entity from the database. Just set the PK fields to a value.
Selfservicing
Deleting the customer "FISSA" from Northwind (doesn't violate any FK constraints)
// C#
CustomerEntity customer = new CustomerEntity();
customer.CustomerID = "FISSA";
customer.Delete();
' VB.NET
Dim customer As New CustomerEntity()
customer.CustomerID = "FISSA"
customer.Delete()
Adapter
// C#
CustomerEntity customer = new CustomerEntity("FISSA");
adapter.DeleteEntity(customer);' VB.NET
Dim customer As New CustomerEntity("FISSA")
adapter.DeleteEntity(customer)
Method 3: use a predicate expression to delete the entity directly.
Selfservicing
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(CustomerFields.CustomerID == "FISSA");
CustomerCollection customers = new CustomerCollection();
customers.DeleteMulti(filter);
' VB.NET
Dim customers As New CustomerCollection()
customers.DeleteMulti((CustomerFields.CustomerID="FISSA"))
Adapter
// C#
IRelationPredicateBucket bucket =
new RelationPredicateBucket((CustomerFields.CustomerID == "FISSA"));
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.DeleteEntitiesDirectly("CustomerEntity", bucket);' VB.NET
Dim bucket As New RelationPredicateBucket( _
(CustomerFields.CustomerID = "FISSA"))
Dim adapter As New DataAccessAdapter()
adapter.DeleteEntitiesDirectly("CustomerEntity", bucket)
How do I pass different connection strings at runtime using Adapter?
All generated code (SelfServicing or Adapter) by default reads the connection string from the *.config file of the application referencing
the generated code.
You can pass a different connection string than the one generated into the app.config file to the DataAccessAdapter's class
constructor. That instance will then use the connection string passed in instead of the connection string defined in the *.config file.
// C#
DataAccessAdapter adapter = new DataAccessAdapter("data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret");' VB.NET
Dim adapter As New DataAccessAdapter("data source=myServer;initial catalog=Northwind;UID=myUserID;PWD=secret")
How do I test if an entity is actually loaded from the database?
Selfservicing
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
bool loadedCorrectly = (customer.Fields.State == EntityState.Fetched);' VB.NET
Dim customer As New CustomerEntity("CHOPS")
Dim loadedCorrectly As Boolean = (customer.Fields.State = EntityState.Fetched)
Adapter
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter();
bool loadedCorrectly = adapter.FetchEntity(customer);
Or..
// C#
CustomerEntity customer = new CustomerEntity("CHOPS");
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntity(customer);
bool loadedCorrectly = (customer.Fields.State == EntityState.Fetched);' VB.NET
Dim customer As New CustomerEntity("CHOPS")
Dim adapter As New DataAccessAdapter()
Dim loadedCorrectly As Boolean = adapter.FetchEntity(customer)
Or..
' VB.NET
Dim customer As New CustomerEntity("CHOPS")
Dim adapter As New DataAccessAdapter()
adapter.FetchEntity(customer)
Dim loadedCorrectly As Boolean = (customer.Fields.State = EntityState.Fetched)
 Note:
|
|
LLBLGen Pro won't change the catalog / schema based on the connectionstring you're specifying. The connection string is used to connect to the database system, though persistence information in the generated code contains the catalog name(s) to target. If you want
to modify the catalog to target with the code at runtime, please specify name overwriting settings, as discussed in
DataAccessAdapter functionality, for sqlserver
and
Application configuration through .config files
|
How can I determine at runtime which fields are the primary key fields of an entity?
References to these fields are stored in the ArrayList returned by the property
PrimaryKeyFields of an entity object.
You can also walk the field objects in the collection returned by the Fields property of an entity object and check the IsPrimaryKey.
How do I insert a NULL value for a field in a new entity?
When you create an entity object, all fields get a default value. When you save the entity (using the SelfServicing method or the Adapter
method), all
changed fields are saved, all
non-changed fields are skipped. This means, that if you for example save a
new CustomerEntity
and you don't set the ContactTitle property to a value, it will not be included in the INSERT query, which will result in a NULL value for ContactTitle in the
database table. If a default constraint would have been defined for that field, the RDBMS would have inserted that value instead of NULL.
How do I prevent that new entities are added to an entity collection in a grid?
The Adapter's EntityCollection class and the SelfServicing entity collection classes
are bound to a grid using an EntityView2 or EntityView object, by default the view returned from the
entity collection's
DefaultView property. These EntityView(2) objects
implement the IBindingList.AllowNew property.
Setting this property to false/False will assure that there will be no 'new' line in the grid bound to the collection object and the
grid is not able to add new entities via the IBindingList interface.
How do I set a time-out setting of 1 minute using SelfServicing?
You can set a timeout (in seconds) for all commands executed using a global setting in the generated
CommonDaoBase class. (HelperClasses namespace).
It doesn't hurt to use a timeout larger than what you actually need, so setting it to 1 minute to make sure a long running query will succeed is not
degrading performance in your application. If a connection to the database is not available, it will however then take longer to timeout. ADO.NET's
default is 15 seconds, the default in the templates is set to 30 seconds.
// C#
// set it to 60 seconds
CommonDaoBase.CommandTimeOut = 60;
' VB.NET
' set it to 60 seconds
CommonDaoBase.CommandTimeOut = 60
How do I update a series of entities directly in the database?
The example below will update all EmployeeID fields in all orders with the value of 5 where the current EmployeeID field is 2.
SelfServicing
// C#
OrderEntity newValues = new OrderEntity();
newValues.EmployeeID = 5;
OrderCollection updater = new OrderCollection();
updater.UpdateMulti(newValues, (OrderFields.EmployeeID == 2));
' VB.NET
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareValuePredicate(OrderFields.EmployeeID, ComparisonOperator.Equal, 2))
Dim updater As New OrderCollection()
updater.UpdateMulti(newValues, filter)
' which is equal to: (VB.NET 2005)
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim updater As New OrderCollection()
updater.UpdateMulti(newValues, (OrderFields.EmployeeID = 2))
Adapter
// C#
OrderEntity newValues = new OrderEntity();
newValues.EmployeeID = 5;
IRelationPredicateBucket filter = new RelationPredicateBucket((OrderFields.EmployeeID == 2));
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.UpdateEntitiesDirectly(newValues, filter);
' VB.NET
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim filter As New RelationPredicateBucket()
filter.PredicateExpression.Add(New FieldCompareValuePredicate(OrderFields.EmployeeID, Nothing, ComparisonOperator.Equal, 2))
Dim adapter As New DataAccessAdapter()
adapter.UpdateEntitiesDirectly(newValues, filter)
' which is equal to (VB.NET 2005)
Dim newValues As New OrderEntity()
newValues.EmployeeID = 5
Dim filter As New RelationPredicateBucket( _
(OrderFields.EmployeeID = 2))
Dim adapter As New DataAccessAdapter()
adapter.UpdateEntitiesDirectly(newValues, filter)
How do I filter on fields in another entity?
Below is an example which retrieves all Customer entities which have bought a given product, in this case the product with ProductID 2
SelfServicing
// C#
IRelationCollection relations = new RelationCollection();
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID);
CustomerCollection customers = new CustomerCollection();
customers.GetMulti((ProductFields.ProductID ==2), relations);
' VB.NET
Dim filter As New PredicateExpression()
Dim relations As New RelationCollection()
relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID)
relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID)
filter.Add(New FieldCompareValue(ProductFieldIndex.ProductID, ComparisonOperator.Equal, 2))
Dim customers As New CustomerCollection()
customers.GetMulti(filter, relations)
' You also could have defined the filter directly: (VB.NET 2005)
customers.GetMulti((ProductFields.ProductID =2), relations)
Adapter
// C#
IRelationPredicateBucket filter = new RelationPredicateBucket();
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID);
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID);
filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID);
filter.PredicateExpression.Add(ProductFields.ProductID == 2);
EntityCollection customers = new CustomerCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, filter);
' VB.NET
Dim filter As New RelationPredicateBucket()
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerID)
filter.Relations.Add(OrderEntity.Relations.OrderDetailsEntityUsingOrderID)
filter.Relations.Add(OrderDetailsEntity.Relations.ProductEntityUsingProductID)
filter.PredicateExpression.Add(New FieldCompareValuePredicate(ProductFields.ProductID, Nothing, ComparisonOperator.Equal, 2))
Dim adapter As New DataAccessAdapter()
Dim customers As New EntityCollection(new CustomerEntityFactory())
adapter.FetchEntityCollection(customers, filter)
' you also could have specified for line A above: (VB.NET 2005)
filter.PredicateExpression.Add(ProductFields.ProductID=2))
How do I handle exceptions which occur during save/delete/fetch actions?
Each exception caught during the execution of the SQL the Dynamic Query Engines generate is wrapped in a
ORMQueryExecutionException exception
object. You can thus create an extra catch clause which solely catches this exception. The ORMQueryExecutionException exception class embeds the
caught exception as the inner exception, the complete SQL executed in text and the parameters collection passed to the query. This ensures you have
all the information needed to fully debug the situation.
How do I implement concurrency control?
Concurrency control is something that can be implemented in various ways. Because there are numerous ways to implement concurrency control (abstract
concurrency control using functionality locking, low level concurrency schemes with optimistic locking or pessimistic locking, all fields filters, timestamp
filters etc.), the generated code offers you the tools to produce the concurrency scheme you want. To implement low level concurrency control like
optimistic locking, predicates are used to limit the scope of a query executed. These predicates are added to the query being executed, for example an
To produce these predicates automatically, it's wise to implement IConcurrencyPredicateFactory for the class(es) you want concurrency control for.
See the sections about concurrency control (
Selfservicing
or
Adapter) for more details about this.
How do I create a m:n relation between two entity objects?
The generated code will not save intermediate table entries in an m:n relation. Consider the two entities Department (DepartmentID (PK, int, identity),
Description) and
Employee (EmployeeID (PK, int, identity), Name). These two entities have an m:n relation, and this results in the third entity DepartmentEmployees
(DepartmentID (PK),
EmployeeID (PK)). To relate a new employee with an existing department follow the example below. It will add a new employee to the existing department
with ID 1. You can of course also set the DepartmentID of the DepartmentEmployeesEntity by hand, avoiding the necessity to fetch the DepartmentEntity
first.
SelfServicing
// C#
DepartmentEntity department = new DepartmentEntity(1);
EmployeeEntity newEmployee = new EmployeeEntity();
newEmployee.Name = "John Doe";
DepartmentEmployeesEntity departmentEmployees = new DepartmentEmployeesEntity();
departmentEmployees.Department = department;
departmentEmployees.Employee = employee;
// save recursively
departmentEmployees.Save(true);
' VB.NET
Dim department As New DepartmentEntity(1)
Dim newEmployee As New EmployeeEntity()
newEmployee.Name = "John Doe"
Dim departmentEmployees As New DepartmentEmployeesEntity()
departmentEmployees.Department = department
departmentEmployees.Employee = employee
' save recursively
departmentEmployees.Save(True)
Adapter
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
DepartmentEntity department = new DepartmentEntity(1);
adapter.FetchEntity(department);
EmployeeEntity newEmployee = new EmployeeEntity();
newEmployee.Name = "John Doe";
DepartmentEmployeesEntity departmentEmployees = new DepartmentEmployeesEntity();
departmentEmployees.Department = department;
departmentEmployees.Employee = employee;
// save recursively
adapter.SaveEntity(departmentEmployees);
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim department As New DepartmentEntity(1)
adapter.FetchEntity(department)
Dim newEmployee As New EmployeeEntity()
newEmployee.Name = "John Doe"
Dim departmentEmployees As New DepartmentEmployeesEntity()
departmentEmployees.Department = department
departmentEmployees.Employee = employee
' save recursively
adapter.SaveEntity(departmentEmployees)
How do I fetch a typed view or typed list in a transaction using SelfServicing?
It can be necessary to fetch a typed view or typed list during a
transaction. Typed lists and typed views are not
addable to a Transaction object, like entities and entity collection classes are. Typed lists and typed views have a Fill() method overload which
accepts a Transaction object. Use that overload to pass an existing transaction object and the Fill() method will use the connection of the
passed in transaction to retrieve the data requested instead of using a new connection. This avoids deadlocks because the writers used in the
transaction block a read (fetch) by the Fill() method.
How do I speed up manual bulk additions to entity collection objects?
If you manually add entities to an EntityCollection object (adapter) or
EntitynameCollection (SelfServicing) object,
you'll run into a performance penalty as the Add() method will check wether the entity added is already in the collection or not. This can lead to severe
slowness in an application if the collection already holds a lot of entity objects (Add() uses List.Contains() which is a .NET method and which performs a linear
search). To prevent this performance
penalty, for example if you don't care if an entity object is added twice, you can set the EntityCollection (Adapter) or
EntitynameCollection (SelfServicing)
property
DoNotPerformAddIfPresent to false.
When you do that, all Add() calls will skip checking if an entity is already in the collection and will be significant faster. FetchEntityCollection() always
sets this property to false before fetching new entities in the collection.
How do I sort the fields in a typed list?
Sorting a typed list is similar to sorting the objects in an entity collection. Say we have a typed list with the fields of 'Customer' and 'Order',
and we want to sort the data on 'OrderDate' descending and 'CompanyName' ascending. First we define the sortexpression. This is the same for
SelfServicing and Adapter.
Selfservicing / Adapter
// C#
SortExpression sorter =
(OrderFields.OrderDate | SortOperator.Descending) & (CustomerFields.CompanyName | SortOperator.Ascending);
' VB.NET
Dim sorter As SortExpression = _
(OrderFields.OrderDate OR SortOperator.Descending) AND (CustomerFields.CompanyName OR SortOperator.Ascending)
This sortexpression can now be passed as the sort expression in a typed list fetch. Below are the typed list fetch statements using SelfServicing
and Adapter. Our typed list is called OrderCustomerTypedList.
Selfservicing
// C#
OrderCustomerTypedList orderCustomerData = new OrderCustomerTypedList();
orderCustomerData.Fill(0, sorter, true, null);
' [VB.NET]
Dim orderCustomerData As New OrderCustomerTypedList()
orderCustomerData.Fill(0, sorter, True, Nothing)
Adapter
// C#
OrderCustomerTypedList orderCustomerData = new OrderCustomerTypedList();
adapter.FetchTypedList(orderCustomerData.GetFieldsInfo(), orderCustomerData,
orderCustomerData.GetRelationInfo(), 0, sorter, true);
' [VB.NET]
Dim orderCustomerData As New OrderCustomerTypedList()
adapter.FetchTypedList(orderCustomerData.GetFieldsInfo(), orderCustomerData, _
orderCustomerData.GetRelationInfo(), 0, sorter, True)
How do I write a filter which does WHERE field = 3 ?
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == 3);
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(YourEntityFields.Field = 3)
How do I write a filter which does WHERE fielda = fieldb ?
Field compares are done using a FieldCompareExpressionPredicate class.
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(OrderFields.OrderDate == OrderFields.ShippingDate);
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(OrderFields.OrderDate = OrderFields.ShippingDate)
How do I write a filter which does WHERE field LIKE '%foo%' ?
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field % "%Foo%");
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(YourEntityFields.Field Mod "%Foo%")
HoHow do I write a filter which does WHERE field BETWEEN 1 AND 10 ?
SelfServicing
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(YourEntityFields.Field, 1, 10));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldBetweenPredicate(YourEntityFields.Field, 1, 10))
Adapterdiv>
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldBetweenPredicate(YourEntityFields.Field, Null, 1, 10));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldBetweenPredicate(YourEntityFields.Field, Nothing, 1, 10))
How do I write a filter which does WHERE field IN (1, 2, 5) ?
There are several ways to specify a range of fixed values: in an Array, in any IList implementing class like ArrayList or List<T> and directly
in the call to the predicate creation. All three ways are shown below.
Using an array
// C#
IPredicateExpression filter = new PredicateExpression();
int[] values = new int[3] {1, 2, 5};
filter.Add(YourEntityFields.Field == values);' VB.NET
Dim filter As New PredicateExpression()
Dim values As Integer() = New Integer(2) {1, 2, 5}
' SelfServicing
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, values))
' SelfServicing
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, Nothing, values))
Using an IList implementing class, e.g. ArrayList
// C#
IPredicateExpression filter = new PredicateExpression();
ArrayList values = new ArrayList();
values.Add(1);
values.Add(2);
values.Add(5);
filter.Add(YourEntityFields.Field == values);
' VB.NET
Dim filter As New PredicateExpression()
Dim values As New ArrayList()
values.Add(1)
values.Add(2)
values.Add(5)
' SelfServicing
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, values))
' Adapter
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, Nothing, values))
Using values directly in the call
SelfServicing
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareRangePredicate(YourEntityFields.Field, 1, 2, 5));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, 1, 2, 5))
Adapter
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareRangePredicate(YourEntityFields.Field, Nothing, 1, 2, 5));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(New FieldCompareRangePredicate(YourEntityFields.Field, Nothing, 1, 2, 5))
How do I write a filter which does WHERE field IN (SELECT fieldb FROM foo) ?
SelfServicing
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
YourEntityFields.Field, FooFields.Fieldb, SetOperator.In, null);
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(new FieldCompareSetPredicate( _
YourEntityFields.Field, FooFields.Fieldb, _
SetOperator.In, Nothing))
Adapter
//// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(new FieldCompareSetPredicate(
YourEntityFields.Field, null, FooFields.Fieldb, null,
SetOperator.In, null));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(new FieldCompareSetPredicate( _
YourEntityFields.Field, Nothing, FooFields.Fieldb, Nothing, _
SetOperator.In, Nothing))
How do I write a filter which does WHERE field IS NULL ?
// C#
IPredicateExpression filter = new PredicateExpression();
filter.Add(YourEntityFields.Field == DBNull.Value));
' VB.NET
Dim filter As New PredicateExpression()
filter.Add(YourEntityFields.Field = DBNull.Value))
How do I use a group by clause in a dynamic list ?
The following query formulates a dynamic list with all managers and the number of employees which are reporting to them. The query
is ran on Northwind, which contains a self-referencing Employees table.
SelfServicing
// C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
RelationCollection relations = new RelationCollection();
relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
TypedListDAO dao = new TypedListDAO();
DataTable tlist = new DataTable();
dao.GetMultiAsDataTable(fields, tlist, 0, null, null, relations, true, groupByClause, null, 0, 0);
' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count)
Dim relations As New RelationCollection()
relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None)
Dim groupByClause As New GroupByCollection()
groupByClause.Add(fields(0))
groupByClause.Add(fields(1))
Dim dao As New TypedListDAO()
Dim tlist As New DataTable()
dao.GetMultiAsDataTable(fields, tlist, 0, Nothing, Nothing, relations, True, groupByClause, Nothing, 0, 0)
Adapter
// C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
DataAccessAdapter adapter = new DataAccessAdapter();
DataTable tlist = new DataTable();
adapter.FetchTypedList(fields, tlist, bucket, 0, null, true, groupByClause);
' VB.NET
Dim fields As New ResultsetFields(3)
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager")
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count)
Dim bucket As New RelationPredicateBucket()
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Manager", JoinHint.None)
Dim groupByClause As New GroupByCollection()
groupByClause.Add(fields(0))
groupByClause.Add(fields(1))
Dim adapter As New DataAccessAdapter()
Dim tlist As New DataTable()
adapter.FetchTypedList(fields, tlist, bucket, 0, Nothing, True, groupByClause)
How do I sort a filled entity collection ?
Sorting an entity collection
client-side ( (i.e.: outside the database), can be done
by using an entity view on the collection and set its sorter.
The sorting is done on a filled collection of CustomerEntity objects, called customers.
Sorting by specifying a property name
The property name can be any property, also a property you've added in code, it doesn't have to be an entity field.
SelfServicing
// C#.
EntityView view = new EntityView(customers);
view.Sorter = (new EntityProperty("propertyName") | SortOperator.Ascending);' VB.NET
Dim view As New EntityView(customers)
view.Sorter = New SortExpression(New SortClause(New EntityProperty("propertyName"), Nothing, SortOperator.Ascending))
Adapter
// C#.
EntityView2 view = new EntityView2(customers);
view.Sorter = (new EntityProperty("propertyName") | SortOperator.Ascending);' VB.NET
Dim view As New EntityView2(customers)
view.Sorter = New SortExpression(New SortClause(New EntityProperty("propertyName"), Nothing, SortOperator.Ascending))
How do I get the MAX(Order.ShippingDate - Order.OrderDate) value from a customer's orders ?
SelfServicing
// C#
OrderCollection orders = new OrderCollection();
int maxValue = (int)orders.GetScalar(OrderFieldIndex.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate),
AggregateFunction.Max, (OrderFields.CustomerId == _customerId));
' VB.NET
Dim orders As New OrderCollection()
Dim maxValue = CInt(orders.GetScalar(OrderFieldIndex.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate), _
AggregateFunction.Max, (OrderFields.CustomerId = _customerId)))
Adapter
// C#
DataAccessAdapter adapter = new DataAccessAdapter();
int maxValue = (int)adapter.GetScalar(OrderFields.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate),
AggregateFunction.Max, (OrderFields.CustomerId == _customerId));
' VB.NET
Dim adapter As New DataAccessAdapter()
Dim maxValue As Integer = CInt(adapter.GetScalar(OrderFieldIndex.OrderId, (OrderFields.ShippedDate - OrderFields.OrderDate), _
AggregateFunction.Max, (OrderFields.CustomerId == _customerId)))
How do I construct a multi-node prefetch path and specify sorting for one node ?
SelfServicing
// C#
IPrefetchPath path = new PrefetchPath(EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers);
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders);
' VB.NET
Dim path As New PrefetchPath(EntityType.EmployeeEntity)
Dim sorter As New SortExpression()
sorter.Add(New SortClause(OrderFields.OrderDate, SortOperator.Descending))
path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers)
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders)
Adapter
// C#
IPrefetchPath2 path = new PrefetchPath2(EntityType.EmployeeEntity);
ISortExpression sorter = new SortExpression();
sorter.Add(OrderFields.OrderDate | SortOperator.Descending);
path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers);
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders);
' VB.NET
Dim path As New PrefetchPath2(EntityType.EmployeeEntity)
Dim sorter As New SortExpression()
sorter.Add(New SortClause(OrderFields.OrderDate, Nothing, SortOperator.Descending))
path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter).SubPath.Add(OrderEntity.PrefetchPathShippers)
path.Add(EmployeeEntity.PrefetchPathCustomers).SubPath.Add(CustomerEntity.PrefetchPathOrders)
Another way to add the Orders and the Shippers nodes is the following. This example allows you to add more nodes to the Orders node.
Selfservicing
// C#
IPrefetchPathElement ordersElement = path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts); // you can now add this subnode
' VB.NET
Dim ordersElement As IPrefetchPathElement = path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts) ' you can now add this subnode
Adapter
// C#
IPrefetchPathElement2 ordersElement = path.Add(EmployeeEntity.PrefetchPathOrders, 0, null, null, sorter);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers);
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts); // you can now add this subnode
' VB.NET
Dim ordersElement As IPrefetchPathElement2 = path.Add(EmployeeEntity.PrefetchPathOrders, 0, Nothing, Nothing, sorter)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathShippers)
ordersElement.SubPath.Add(OrderEntity.PrefetchPathProducts) ' you can now add this subnode
How do I add an entity A to an entity B's collection of A's if A and B have an m:n relation ?
Say you want to add an Employee to a Department and Employee and Department have an m:n relation. This means that there is an intermediate
entity DepartmentEmployees which defines the m:n relation with a foreign key to both Employee and Department. Employee has a collection Departments
which results in all Department entities the Employee works for and Department has a collection Employees which results in all Employees working
for the Department. In LLBLGen Pro, m:n relations are read-only, therefore, to add an Employee to a Department, you've to use the following steps.
The example uses
department and
employee which are filled in DepartmentEntity and EmployeeEntity instances. You can also use new
entities, this doesn't matter.
Selfservicing
// C#
DepartmentEmployees newDepEmp = new DepartmentEmployees();
newDepEmp.Employee = employee;
newDepEmp.Department = department;
// the 3 entities are now linked. We can now save recursively any of the three (doesn't matter)
// to save the relation in the database.
employee.Save(true);
' VB.NET
Dim newDepEmp As New DepartmentEmployees()
newDepEmp.Employee = employee
newDepEmp.Department = department
' the 3 entities are now linked. We can now save recursively any of the three (doesn't matter)
' to save the relation in the database.
employee.Save(True)
Adapter
// C#
DepartmentEmployees newDepEmp = new DepartmentEmployees();
newDepEmp.Employee = employee;
newDepEmp.Department = department;
// the 3 entities are now linked. We can now save recursively any of the three (doesn't matter)
// to save the relation in the database.
adapter.SaveEntity(employee);
' VB.NET
Dim newDepEmp As New DepartmentEmployees()
newDepEmp.Employee = employee
newDepEmp.Department = department
' the 3 entities are now linked. We can now save recursively any of the three (doesn't matter)
' to save the relation in the database.
adapter.SaveEntity(employee)
Keep in mind that the m:n relation is then saved in the database, but the situation in memory isn't updated, i.e.:
employee.Departments doesn't contain 'department'. This is by design, as the m:n relation is based on a 3rd entity,
which can be altered by any thread in the system, making the m:n relation, which is a result of the values of the intermediate entity,
a 'view' of a situation at a given point in time.
